﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DataAccess;
using System.Collections;
using System.Configuration;
using System.Data.SqlClient;
using System.Security;
using System.Security.Permissions;
using System.Reflection;
using System.Runtime.CompilerServices;



namespace Business {
    public class ReportServices {

        //private CreateConnection cn = new CreateConnection();


        public DataTable getDateByMachine(int machine_id) {
            CreateConnection cn = new CreateConnection();
            string query = "SELECT ComponentStatus.dateRecorded " +
                           "FROM ComponentStatus , Components , Machines " +
                           "WHERE ComponentStatus.component_id = Components.component_id " +
                           "AND Components.machine_id = Machines.machine_id " +
                           "AND (Components.machine_id = " + machine_id.ToString() +
                           " Group by ComponentStatus.dateRecorded;";
            DataTable tb = new DataTable();
            tb = cn.CreateDataTable(query);
            return tb;
        }

        public DataTable getSeverity() {
            CreateConnection cn = new CreateConnection();
            string severity = "select statusName,status_id,statusDescription from Status;";
            DataTable dt = new DataTable();
            dt = cn.CreateDataTable(severity);
            return dt;
        }
        public DataTable getChildrenClientByClientID(int client_id) {
            CreateConnection cn = new CreateConnection();
            string children = "select client_id,clientName from clients where parent_client_id =" + client_id + ";";
            DataTable dt = new DataTable();
            dt = cn.CreateDataTable(children);
            return dt;
        }
        //public DataTable getLatestDateByClientID(int client_id) {
        //    CreateConnection cn = new CreateConnection();
        //    string query = " SELECT        CONVERT(varchar(10), dateRecorded, 3) AS Expr1 " +
        //                   " FROM         (SELECT        TOP (1) ComponentStatus.dateRecorded " +
        //                   " FROM            Clients INNER JOIN" +
        //                                           " Sites ON Clients.client_id = Sites.client_id INNER JOIN" +
        //                                           " Machines ON Sites.site_id = Machines.site_id INNER JOIN" +
        //                                           " Components ON Machines.machine_id = Components.machine_id INNER JOIN" +
        //                                           " ComponentStatus ON Components.component_id = ComponentStatus.component_id" +
        //                  " WHERE        (Clients.client_id = " + client_id + ")" +
        //                  " ORDER BY ComponentStatus.dateRecorded DESC) AS q1";
        //    DataTable dt = new DataTable();
        //    dt = cn.CreateDataTable(query);
        //    return dt;
        //}
        public DataTable getLatestDateByClientID(int client_id) {
            CreateConnection cn = new CreateConnection();
            string query = " SELECT        CONVERT(varchar(10), licenced_start, 3) AS Expr1 " +
                           " FROM            Clients " +
                          " WHERE        (Clients.client_id = " + client_id + ")";
            DataTable dt = new DataTable();
            dt = cn.CreateDataTable(query);
            return dt;
        }
        public DataTable getSiteByCLientID(int client_id) {
            CreateConnection cn = new CreateConnection();
            string site = "select siteName,site_id from Sites where status = 'Activated' and client_id = " + client_id.ToString() + ";";
            // string site = "SELECT siteName, site_id FROM     Sites WHERE        (status = 'Activated') AND (client_id = 1005)";
            DataTable dt = new DataTable();
            dt = cn.CreateDataTable(site);
            return dt;
        }
        public string getMachineNamebyID(int machine_id) {
            CreateConnection cn = new CreateConnection();

            string query = "select machineName from machines where machine_id=" + machine_id;
            DataTable dt = new DataTable();
            dt = cn.CreateDataTable(query);
            string machineName = "";
            foreach (DataRow r in dt.Rows) {
                machineName = r["machineName"].ToString();
            }
            return machineName;
        }
        public DataSet getMachinesBySites(DataTable sites) {
            CreateConnection cn = new CreateConnection();
            DataSet ds = new DataSet();
            // CreateConnection cn; 
            //ArrayList query = new ArrayList();
            //string query = "";
            foreach (DataRow r in sites.Rows) {
                string site_id = r["site_id"].ToString();
                string machine = "select m.machine_id, m.machineName,t.MachineType_name " +
                               "from Machines m, MachineType t " +
                               "where (m.site_id = " + site_id + ") " +
                               "and (m.status='Activated') " +
                               "and (m.machineType_id =t.machineType_id) order by t.MachineType_name;";
                DataTable dt = new DataTable();
                //  cn =new CreateConnection();
                dt = cn.CreateDataTable(machine);
                ds.Tables.Add(dt);
            }
            return ds;
        }
        public DataTable getComponentsByMachinesAndStatusByDate(DataTable machines, DataTable Status, string date)//DateTime date)
        {
            CreateConnection cn = new CreateConnection();
            DataTable components = new DataTable();
            string query = "";
            string machineList = "";
            string statusList = "";
            bool first = true;
            foreach (DataRow r in machines.Rows) {
                string machine_id = r["machine_id"].ToString();
                if (first == true) {
                    machineList = machineList + "(" + machine_id;
                    first = false;
                } else
                    machineList = machineList + "," + machine_id;
            }
            machineList = machineList + ")";
            first = true;
            foreach (DataRow r in Status.Rows) {
                string status_id = r["status_id"].ToString();
                if (first == true) {
                    statusList = statusList + "(" + status_id;
                    first = false;
                } else {
                    statusList = statusList + "," + status_id;
                }
            }
            statusList = statusList + ")";
            /*      
                  query = query+ "SELECT DISTINCT c.component_id, r.status_id, f.faultName, c.componentName, c.machine_id, s.comments "+
                                 "FROM FaultRecommendation AS r INNER JOIN "+
                                      "FaultsCondition AS f ON r.fault_id = f.fault_id CROSS JOIN "+
                                      "Components AS c INNER JOIN "+
                                      "ComponentStatus AS s ON c.component_id = s.component_id "+
                                 "WHERE     (s.dateRecorded = CONVERT(datetime, '"+date+"', 103)) "+
                                            "AND (c.machine_id IN "+ machineList+") "+
                                            "AND (r.status_id IN "+ statusList+") "+
                                            "AND (s.recommendID_1 = r.recommend_id) "+
                                      " OR" +
                                            "(s.dateRecorded = CONVERT(datetime, '"+date+"', 103)) "+
                                            "AND (c.machine_id IN "+ machineList+") "+
                                            "AND (r.status_id IN "+ statusList+") "+
                                            "AND (s.recommendID_2 = r.recommend_id) "+
                                      " OR" +
                                            "(s.dateRecorded = CONVERT(datetime, '"+date+"', 103)) "+
                                            "AND (c.machine_id IN "+ machineList+") "+
                                            "AND (r.status_id IN "+ statusList +") "+
                                            "AND (s.recommendID_3 = r.recommend_id) ";*/


            query = query +
"SELECT  ci.component_id, f.faultName, ci.componentName, ci.comments, FaultRecommendation.status_id,ci.machine_id  " +

        "FROM    FaultsCondition AS f INNER JOIN FaultRecommendation ON f.fault_id = FaultRecommendation.fault_id CROSS JOIN " +

        "	(SELECT DISTINCT c.component_id, c.componentName, c.machine_id, s.comments  " +
        "	 FROM            FaultRecommendation AS r CROSS JOIN   " +
        "		         Components AS c INNER JOIN        " +
        "			 ComponentStatus AS s ON c.component_id = s.component_id   " +
        "	 WHERE        ((convert(varchar(10),s.dateRecorded,3) = '" + date + "') " +
        "			AND (c.machine_id IN " + machineList + ") " +
        "			AND (r.status_id IN " + statusList + ") " +
        "			AND (s.recommendID_1 = r.recommend_id)) " +

        "		OR  ((convert(varchar(10),s.dateRecorded,3) =  '" + date + "') " +
        "			AND (c.machine_id IN " + machineList + ")" +
        "			AND (r.status_id IN " + statusList + ") " +
        "			AND (s.recommendID_2 = r.recommend_id) ) " +


        "		OR ( (convert(varchar(10),s.dateRecorded,3) =  '" + date + "') " +
        "			AND (c.machine_id IN " + machineList + ")" +
        "			AND (r.status_id IN " + statusList + ")" +
        "			AND (s.recommendID_3 = r.recommend_id))) AS ci " +
        "	INNER JOIN ComponentStatus ON ci.component_id = ComponentStatus.component_id " +

        " WHERE        ((convert(varchar(10),ComponentStatus.dateRecorded,3) =  '" + date + "')" +
        "		AND (ComponentStatus.recommendID_1 = FaultRecommendation.recommend_id) ) " +
        "	OR ( (convert(varchar(10),ComponentStatus.dateRecorded,3) =  '" + date + "')" +
        "		AND (ComponentStatus.recommendID_2 = FaultRecommendation.recommend_id) ) " +
        "	OR ((convert(varchar(10),ComponentStatus.dateRecorded,3) =  '" + date + "') " +
        "		AND (ComponentStatus.recommendID_3 = FaultRecommendation.recommend_id))";


            //query = query + " SELECT  ci.component_id, f.faultName, ci.componentName, ci.comments, FaultRecommendation.status_id,ci.machine_id " +
            //         " FROM    FaultsCondition AS f INNER JOIN" +
            //                 " FaultRecommendation ON f.fault_id = FaultRecommendation.fault_id CROSS JOIN" +
            //                 " (SELECT DISTINCT c.component_id, c.componentName, c.machine_id, s.comments" +
            //                 "  FROM            FaultRecommendation AS r CROSS JOIN " +
            //                               "          Components AS c INNER JOIN " +
            //                                "         ComponentStatus AS s ON c.component_id = s.component_id " +
            //                 "  WHERE       ( (c.machine_id IN " + machineList + ")" +
            //                                       "  AND (r.status_id IN " + statusList + ") AND (s.recommendID_1 = r.recommend_id)) OR" +
            //                                       " (  (c.machine_id IN " + machineList + ") " +
            //                                        " AND (r.status_id IN " + statusList + ") AND (s.recommendID_2 = r.recommend_id)) OR" +
            //                                        " ((c.machine_id IN " + machineList + ") " +
            //                                        " AND (r.status_id IN " + statusList + ") AND (s.recommendID_3 = r.recommend_id))) AS ci INNER JOIN" +
            //                                        " ComponentStatus ON ci.component_id = ComponentStatus.component_id" +
            //        " WHERE         AND (ComponentStatus.recommendID_1 = FaultRecommendation.recommend_id) OR" +
            //                    "  (convert(datetime,ComponentStatus.dateRecorded,101) = CONVERT(datetime, '" + date + "', 101)) AND (ComponentStatus.recommendID_2 = FaultRecommendation.recommend_id) OR" +
            //                    " (convert(datetime,ComponentStatus.dateRecorded,101) = CONVERT(datetime, '" + date + "', 101)) AND (ComponentStatus.recommendID_3 = FaultRecommendation.recommend_id)";




            components = cn.CreateDataTable(query);

            return components;
        }


        public DataTable getComponentsByMachinesAndStatusByDateyear(DataTable machines, DataTable Status, string date)//DateTime date)
        {
            CreateConnection cn = new CreateConnection();
            DataTable components = new DataTable();
            string query = "";
            string machineList = "";
            string statusList = "";
            bool first = true;
            foreach (DataRow r in machines.Rows) {
                string machine_id = r["machine_id"].ToString();
                if (first == true) {
                    machineList = machineList + "(" + machine_id;
                    first = false;
                } else
                    machineList = machineList + "," + machine_id;
            }
            machineList = machineList + ")";
            first = true;
            foreach (DataRow r in Status.Rows) {
                string status_id = r["status_id"].ToString();
                if (first == true) {
                    statusList = statusList + "(" + status_id;
                    first = false;
                } else {
                    statusList = statusList + "," + status_id;
                }
            }
            statusList = statusList + ")";
            /*      
                  query = query+ "SELECT DISTINCT c.component_id, r.status_id, f.faultName, c.componentName, c.machine_id, s.comments "+
                                 "FROM FaultRecommendation AS r INNER JOIN "+
                                      "FaultsCondition AS f ON r.fault_id = f.fault_id CROSS JOIN "+
                                      "Components AS c INNER JOIN "+
                                      "ComponentStatus AS s ON c.component_id = s.component_id "+
                                 "WHERE     (s.dateRecorded = CONVERT(datetime, '"+date+"', 103)) "+
                                            "AND (c.machine_id IN "+ machineList+") "+
                                            "AND (r.status_id IN "+ statusList+") "+
                                            "AND (s.recommendID_1 = r.recommend_id) "+
                                      " OR" +
                                            "(s.dateRecorded = CONVERT(datetime, '"+date+"', 103)) "+
                                            "AND (c.machine_id IN "+ machineList+") "+
                                            "AND (r.status_id IN "+ statusList+") "+
                                            "AND (s.recommendID_2 = r.recommend_id) "+
                                      " OR" +
                                            "(s.dateRecorded = CONVERT(datetime, '"+date+"', 103)) "+
                                            "AND (c.machine_id IN "+ machineList+") "+
                                            "AND (r.status_id IN "+ statusList +") "+
                                            "AND (s.recommendID_3 = r.recommend_id) ";*/


            query = query +
"SELECT  ci.component_id, f.faultName, ci.componentName, ci.comments, FaultRecommendation.status_id,ci.machine_id  " +

        "FROM    FaultsCondition AS f INNER JOIN FaultRecommendation ON f.fault_id = FaultRecommendation.fault_id CROSS JOIN " +

        "	(SELECT DISTINCT c.component_id, c.componentName, c.machine_id, s.comments  " +
        "	 FROM            FaultRecommendation AS r CROSS JOIN   " +
        "		         Components AS c INNER JOIN        " +
        "			 ComponentStatus AS s ON c.component_id = s.component_id   " +
        "	 WHERE        ((convert(varchar(10),s.dateRecorded,103) = '" + date + "') " +
        "			AND (c.machine_id IN " + machineList + ") " +
        "			AND (r.status_id IN " + statusList + ") " +
        "			AND (s.recommendID_1 = r.recommend_id)) " +

        "		OR  ((convert(varchar(10),s.dateRecorded,103) =  '" + date + "') " +
        "			AND (c.machine_id IN " + machineList + ")" +
        "			AND (r.status_id IN " + statusList + ") " +
        "			AND (s.recommendID_2 = r.recommend_id) ) " +


        "		OR ( (convert(varchar(10),s.dateRecorded,103) =  '" + date + "') " +
        "			AND (c.machine_id IN " + machineList + ")" +
        "			AND (r.status_id IN " + statusList + ")" +
        "			AND (s.recommendID_3 = r.recommend_id))) AS ci " +
        "	INNER JOIN ComponentStatus ON ci.component_id = ComponentStatus.component_id " +

        " WHERE        ((convert(varchar(10),ComponentStatus.dateRecorded,103) =  '" + date + "')" +
        "		AND (ComponentStatus.recommendID_1 = FaultRecommendation.recommend_id) ) " +
        "	OR ( (convert(varchar(10),ComponentStatus.dateRecorded,103) =  '" + date + "')" +
        "		AND (ComponentStatus.recommendID_2 = FaultRecommendation.recommend_id) ) " +
        "	OR ((convert(varchar(10),ComponentStatus.dateRecorded,103) =  '" + date + "') " +
        "		AND (ComponentStatus.recommendID_3 = FaultRecommendation.recommend_id))";


            //query = query + " SELECT  ci.component_id, f.faultName, ci.componentName, ci.comments, FaultRecommendation.status_id,ci.machine_id " +
            //         " FROM    FaultsCondition AS f INNER JOIN" +
            //                 " FaultRecommendation ON f.fault_id = FaultRecommendation.fault_id CROSS JOIN" +
            //                 " (SELECT DISTINCT c.component_id, c.componentName, c.machine_id, s.comments" +
            //                 "  FROM            FaultRecommendation AS r CROSS JOIN " +
            //                               "          Components AS c INNER JOIN " +
            //                                "         ComponentStatus AS s ON c.component_id = s.component_id " +
            //                 "  WHERE       ( (c.machine_id IN " + machineList + ")" +
            //                                       "  AND (r.status_id IN " + statusList + ") AND (s.recommendID_1 = r.recommend_id)) OR" +
            //                                       " (  (c.machine_id IN " + machineList + ") " +
            //                                        " AND (r.status_id IN " + statusList + ") AND (s.recommendID_2 = r.recommend_id)) OR" +
            //                                        " ((c.machine_id IN " + machineList + ") " +
            //                                        " AND (r.status_id IN " + statusList + ") AND (s.recommendID_3 = r.recommend_id))) AS ci INNER JOIN" +
            //                                        " ComponentStatus ON ci.component_id = ComponentStatus.component_id" +
            //        " WHERE         AND (ComponentStatus.recommendID_1 = FaultRecommendation.recommend_id) OR" +
            //                    "  (convert(datetime,ComponentStatus.dateRecorded,101) = CONVERT(datetime, '" + date + "', 101)) AND (ComponentStatus.recommendID_2 = FaultRecommendation.recommend_id) OR" +
            //                    " (convert(datetime,ComponentStatus.dateRecorded,101) = CONVERT(datetime, '" + date + "', 101)) AND (ComponentStatus.recommendID_3 = FaultRecommendation.recommend_id)";




            components = cn.CreateDataTable(query);

            return components;
        }


        public DataTable getComponentsByMachinesAndStatusByDate(DataTable machines, DataTable Status, DateTime date) {
            CreateConnection cn = new CreateConnection();
            DataTable components = new DataTable();
            string query = "";
            string machineList = "";
            string statusList = "";
            bool first = true;
            foreach (DataRow r in machines.Rows) {
                string machine_id = r["machine_id"].ToString();
                if (first == true) {
                    machineList = machineList + "(" + machine_id;
                    first = false;
                } else
                    machineList = machineList + "," + machine_id;
            }
            machineList = machineList + ")";
            first = true;
            foreach (DataRow r in Status.Rows) {
                string status_id = r["status_id"].ToString();
                if (first == true) {
                    statusList = statusList + "(" + status_id;
                    first = false;
                } else {
                    statusList = statusList + "," + status_id;
                }
            }
            statusList = statusList + ")";
            /*      
                  query = query+ "SELECT DISTINCT c.component_id, r.status_id, f.faultName, c.componentName, c.machine_id, s.comments "+
                                 "FROM FaultRecommendation AS r INNER JOIN "+
                                      "FaultsCondition AS f ON r.fault_id = f.fault_id CROSS JOIN "+
                                      "Components AS c INNER JOIN "+
                                      "ComponentStatus AS s ON c.component_id = s.component_id "+
                                 "WHERE     (s.dateRecorded = CONVERT(datetime, '"+date+"', 103)) "+
                                            "AND (c.machine_id IN "+ machineList+") "+
                                            "AND (r.status_id IN "+ statusList+") "+
                                            "AND (s.recommendID_1 = r.recommend_id) "+
                                      " OR" +
                                            "(s.dateRecorded = CONVERT(datetime, '"+date+"', 103)) "+
                                            "AND (c.machine_id IN "+ machineList+") "+
                                            "AND (r.status_id IN "+ statusList+") "+
                                            "AND (s.recommendID_2 = r.recommend_id) "+
                                      " OR" +
                                            "(s.dateRecorded = CONVERT(datetime, '"+date+"', 103)) "+
                                            "AND (c.machine_id IN "+ machineList+") "+
                                            "AND (r.status_id IN "+ statusList +") "+
                                            "AND (s.recommendID_3 = r.recommend_id) ";*/


            query = query +
"SELECT  ci.component_id, f.faultName, ci.componentName, ci.comments, FaultRecommendation.status_id,ci.machine_id  " +

        "FROM    FaultsCondition AS f INNER JOIN FaultRecommendation ON f.fault_id = FaultRecommendation.fault_id CROSS JOIN " +

        "	(SELECT DISTINCT c.component_id, c.componentName, c.machine_id, s.comments  " +
        "	 FROM            FaultRecommendation AS r CROSS JOIN   " +
        "		         Components AS c INNER JOIN        " +
        "			 ComponentStatus AS s ON c.component_id = s.component_id   " +
        "	 WHERE        ((convert(date,s.dateRecorded,103) = CONVERT(date,'" + date + "', 103))" +
        "			AND (c.machine_id IN " + machineList + ") " +
        "			AND (r.status_id IN " + statusList + ") " +
        "			AND (s.recommendID_1 = r.recommend_id)) " +

        "		OR  ((convert(date,s.dateRecorded,103) = CONVERT(date, '" + date + "', 103)) " +
        "			AND (c.machine_id IN " + machineList + ")" +
        "			AND (r.status_id IN " + statusList + ") " +
        "			AND (s.recommendID_2 = r.recommend_id) ) " +


        "		OR ( (convert(date,s.dateRecorded,103) = CONVERT(date, '" + date + "', 103)) " +
        "			AND (c.machine_id IN " + machineList + ")" +
        "			AND (r.status_id IN " + statusList + ")" +
        "			AND (s.recommendID_3 = r.recommend_id))) AS ci " +
        "	INNER JOIN ComponentStatus ON ci.component_id = ComponentStatus.component_id " +

        " WHERE        ((convert(date,ComponentStatus.dateRecorded,103) = CONVERT(date, '" + date + "', 103))" +
        "		AND (ComponentStatus.recommendID_1 = FaultRecommendation.recommend_id) ) " +
        "	OR ( (convert(date,ComponentStatus.dateRecorded,103) = CONVERT(date, '" + date + "', 103))" +
        "		AND (ComponentStatus.recommendID_2 = FaultRecommendation.recommend_id) ) " +
        "	OR ((convert(date,ComponentStatus.dateRecorded,103) = CONVERT(date, '" + date + "', 103)) " +
        "		AND (ComponentStatus.recommendID_3 = FaultRecommendation.recommend_id))";


            //query = query + " SELECT  ci.component_id, f.faultName, ci.componentName, ci.comments, FaultRecommendation.status_id,ci.machine_id " +
            //         " FROM    FaultsCondition AS f INNER JOIN" +
            //                 " FaultRecommendation ON f.fault_id = FaultRecommendation.fault_id CROSS JOIN" +
            //                 " (SELECT DISTINCT c.component_id, c.componentName, c.machine_id, s.comments" +
            //                 "  FROM            FaultRecommendation AS r CROSS JOIN " +
            //                               "          Components AS c INNER JOIN " +
            //                                "         ComponentStatus AS s ON c.component_id = s.component_id " +
            //                 "  WHERE       ( (c.machine_id IN " + machineList + ")" +
            //                                       "  AND (r.status_id IN " + statusList + ") AND (s.recommendID_1 = r.recommend_id)) OR" +
            //                                       " (  (c.machine_id IN " + machineList + ") " +
            //                                        " AND (r.status_id IN " + statusList + ") AND (s.recommendID_2 = r.recommend_id)) OR" +
            //                                        " ((c.machine_id IN " + machineList + ") " +
            //                                        " AND (r.status_id IN " + statusList + ") AND (s.recommendID_3 = r.recommend_id))) AS ci INNER JOIN" +
            //                                        " ComponentStatus ON ci.component_id = ComponentStatus.component_id" +
            //        " WHERE         AND (ComponentStatus.recommendID_1 = FaultRecommendation.recommend_id) OR" +
            //                    "  (convert(datetime,ComponentStatus.dateRecorded,101) = CONVERT(datetime, '" + date + "', 101)) AND (ComponentStatus.recommendID_2 = FaultRecommendation.recommend_id) OR" +
            //                    " (convert(datetime,ComponentStatus.dateRecorded,101) = CONVERT(datetime, '" + date + "', 101)) AND (ComponentStatus.recommendID_3 = FaultRecommendation.recommend_id)";




            components = cn.CreateDataTable(query);

            return components;
        }


        /*   public DataTable getComponentsByMachinesAndStatusByDate(DataTable machines, DataTable Status, string date)
           {
               DataTable components = new DataTable();
               string query = "";
               string machineList = "";
               string statusList = "";
               bool first = true;
               foreach (DataRow r in machines.Rows)
               {
                   string machine_id = r["machine_id"].ToString();
                   if (first == true)
                   {
                       machineList = machineList + "(" + machine_id;
                       first = false;
                   }
                   else
                       machineList = machineList + "," + machine_id;
               }
               machineList = machineList + ")";
               first = true;
               foreach (DataRow r in Status.Rows)
               {
                   string status_id = r["status_id"].ToString();
                   if (first == true)
                   {
                       statusList = statusList + "(" + status_id;
                       first = false;
                   }
                   else
                   {
                       statusList = statusList + "," + status_id;
                   }
               }
               statusList = statusList + ")";

               query = query + "SELECT DISTINCT c.component_id, r.status_id, f.faultName, c.componentName, c.machine_id, s.comments " +
                              "FROM FaultRecommendation AS r INNER JOIN " +
                                   "FaultsCondition AS f ON r.fault_id = f.fault_id CROSS JOIN " +
                                   "Components AS c INNER JOIN " +
                                   "ComponentStatus AS s ON c.component_id = s.component_id " +
                              "WHERE     (s.dateRecorded = CONVERT(datetime, '" + date + "', 103)) " +
                                         "AND (c.machine_id IN " + machineList + ") " +
                                         "AND (r.status_id IN " + statusList + ") " +
                                         "AND (s.recommendID_1 = r.recommend_id) " +
                                   " OR" +
                                         "(s.dateRecorded = CONVERT(datetime, '" + date + "', 103)) " +
                                         "AND (c.machine_id IN " + machineList + ") " +
                                         "AND (r.status_id IN " + statusList + ") " +
                                         "AND (s.recommendID_2 = r.recommend_id) " +
                                   " OR" +
                                         "(s.dateRecorded = CONVERT(datetime, '" + date + "', 103)) " +
                                         "AND (c.machine_id IN " + machineList + ") " +
                                         "AND (r.status_id IN " + statusList + ") " +
                                         "AND (s.recommendID_3 = r.recommend_id) ";

               components = cn.CreateDataTable(query);

               return components;
           }*/

        public DataSet getMachinesBySites1(DataTable sites) {
            DataSet ds = new DataSet();
            CreateConnection cn;
            ArrayList query = new ArrayList();
            //string query = "";
            foreach (DataRow r in sites.Rows) {
                string site_id = r["site_id"].ToString();
                string machine = "select m.machine_id, m.machineName,t.MachineType_name " +
                               "from Machines m, MachineType t " +
                               "where (m.site_id = " + site_id + ") " +
                               "and (m.status='Activated') " +
                               "and (m.machineType_id =t.machineType_id) order by t.MachineType_name;";
                query.Add(machine);

            }
            cn = new CreateConnection();
            ds = cn.CreateDataSet(query);
            int i = ds.Tables.Count;
            return ds;
        }
        public DataTable getMachineOfSites(DataTable sites) {
            CreateConnection cn = new CreateConnection();

            DataTable machines = new DataTable();
            string query = "";
            query = "select m.machine_id, m.machineName,t.MachineType_name, m.site_id,t.machineType_id " +
                               "from Machines m, MachineType t " +
                               "where " +
                               "(m.status='Activated') " +
                               "and (m.machineType_id =t.machineType_id) and (";
            int i = 0;
            foreach (DataRow r in sites.Rows) {
                string site_id = r["site_id"].ToString();
                if (i != 0) {
                    query = query + " or ";
                }
                query = query + " (m.site_id = " + site_id + ")";
                i++;
            }
            query = query + ") ;";
            //  CreateConnection cn = new CreateConnection();
            machines = cn.CreateDataTable(query);
            return machines;
        }

        DataTable getComponentsOfSite(DataRow[] machines) {
            DataTable components = new DataTable();

            return components;
        }
        public DataTable getClientsByParent(int parentID) {
            CreateConnection cn = new CreateConnection();

            DataTable clients = new DataTable();
            string query = "";
            query = "select clientName, client_id from Clients where parent_client_id = " + parentID.ToString() + " ;";
            clients = cn.CreateDataTable(query);
            return clients;
        }
        public DataTable getcomponentsByMachine(int machine_id) {
            CreateConnection cn = new CreateConnection();

            DataTable components = new DataTable();
            string query = "select componentName, component_id from components where machine_id = " + machine_id.ToString() + " ;";
            components = cn.CreateDataTable(query);
            return components;
        }
        public DataTable getDateByMachineID(int machine_id) {
            CreateConnection cn = new CreateConnection();
            DataTable dt = new DataTable();
            string query = "select distinct dateRecorded from ComponentStatus, components where componentstatus.component_id=components.component_id and components.machine_id=" + machine_id + " order by dateRecorded DESC ";
            dt = cn.CreateDataTable(query);
            return dt;
        }
    }
}

